﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Utils.DatabaseUtils;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;

namespace Utils.DC.File.UI
{
    public class SetProperties
    {
        private string strTableName = "";
        private string strSheetName = "";
        private string strCsvDirOnServer = "";
        private string strExportCSVasName = "";
        private string strExportTableName = "";
        private string strExportCsvDirOnServer = "";
        private string strExportAsCsvOrText = "";
        private bool blnDropExistingTable = false;
        private bool blnSaveFileOnServer = false;
        private FileInfo fil;

        public string TableName { get { return strTableName; } set { strTableName = value; } }
        public string SheetName { get { return strSheetName; } set { strSheetName = value; } }
        public string CsvDirOnServer { get { return strCsvDirOnServer; } set { strCsvDirOnServer = value; } }
        public bool DropExistingTable { get { return blnDropExistingTable; } set { blnDropExistingTable = value; } }
        public bool SaveFileOnServer { get { return blnSaveFileOnServer; } set { blnSaveFileOnServer = value; } }
        public FileInfo FileInformation { get { return fil; } set { fil = value; } }
        public string ExportCSVasName { get { return strExportCSVasName; } set { strExportCSVasName = value; } }
        public string ExportTableName { get { return strExportTableName; } set { strExportTableName = value; } }
        public string ExportCSVDirOnServer { get { return strExportCsvDirOnServer; } set { strExportCsvDirOnServer = value; } }
        public string ExportAsCsvOrText { get { return strExportAsCsvOrText; } set { strExportAsCsvOrText = value; } }
    }

    public class ExcelWorker
    {
        #region Class Constructor
        public ExcelWorker()
        {
        }
        #endregion

        #region Variable Declaration
        private string ConnSQL = "";
        private string ConnCSV = "";
        string strTableName = "";
        string strServerPath = "";
        string strCsvDirOnServer = "";
        private string strExportCSV = "";
        private string strExportTable = "";
        private string strExportCSVDir = "";
        private string strExportAsCsvOrText = "";
        public DataTable dtResult = null;
        bool blnDropExistingTable = false;
        bool blnSaveFileOnServer = false;
        FileInfo fi;
        #endregion

        #region Import Functionality

        /// <summary>
        /// Duyet Tung Dong Cua File Excel
        /// Tim Dong Va Cot Bat Dau Cua Bang Excel
        /// </summary>
        /// <param name="tableRows">Mang Cac Hang Cua File Excel</param>
        /// <param name="rowStart">Hang Bat Dau</param>
        /// <param name="coloumnStart">Cot Bat Dau</param>
        /// 
        private void GetStartData(DataRowCollection tableRows, ref int rowStart, ref int coloumnStart)
        {
            object[] objRow = null;
            for (int i = 0; i < tableRows.Count; i++)
            {
                objRow = tableRows[i].ItemArray;
                for (int j = 0; j < objRow.Length; j++)
                {
                    if (objRow[j].ToString().Trim() == "STT")
                    {
                        rowStart = i;
                        coloumnStart = j;
                        return;
                    }
                }
            }
        }

        public string GenerateTable(SetProperties Properties)
        {
            string strreturn = "";
            try
            {
                blnSaveFileOnServer = Properties.SaveFileOnServer;
                blnDropExistingTable = Properties.DropExistingTable;
                strCsvDirOnServer = Properties.CsvDirOnServer;
                strTableName = Properties.TableName;
                fi = Properties.FileInformation;

                DataSet dsCSV = new DataSet();
                dsCSV = GetExcelTable(fi,Properties.SheetName);
                DataColumnCollection tableColumns;
                DataRowCollection tableRows;
                //try
                //{
                     tableColumns = dsCSV.Tables[0].Columns;
                     tableRows = dsCSV.Tables[0].Rows;

                //}
                //catch
                //{
                //    return ConnectFile1(fi);
                //}
                    if( strTableName.Trim() == "Images" )
                    {
                        //strreturn = InsertRecordsNhanVien(dsCSV.Tables[0]);
                    }

                return strreturn;

            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

        /// <summary>
        /// Ham dung de lay ve toan bo thong tin cua file excel
        /// </summary>
        /// <param name="Properties"></param>
        /// <param name="dsCSV"></param>
        /// <returns></returns>
        public DataSet LoadExcel(SetProperties Properties, int start, int end)
        {
            string strreturn = "";
            DataSet dsExcelFile = new DataSet();
            try
            {
                blnSaveFileOnServer = Properties.SaveFileOnServer;
                blnDropExistingTable = Properties.DropExistingTable;
                strCsvDirOnServer = Properties.CsvDirOnServer;
                strTableName = Properties.TableName;
                fi = Properties.FileInformation;

                DataTable dtSheetList = GetSheetList(fi);
                dtSheetList.TableName = "SheetList";
                dsExcelFile.Tables.Add(dtSheetList);

                DataSet dsCSV = null;
                DataSet tmp = null;
                foreach (DataRow row in dtSheetList.Rows)
                {
                    tmp = GetExcelTable(fi, row["TABLE_NAME"].ToString(), start, end);
                    if (dsCSV != null)
                    {
                        if (TableLib.HasData(tmp))
                            dsCSV.Merge(tmp);
                    }
                    else
                    {
                        dsCSV = tmp;
                    }
                    //DataTable dt = TrimExcelFile(dsCSV.Tables[0]);

                    /*DataColumnCollection tableColumns;
                    DataRowCollection tableRows;

                    tableColumns = dt.Columns;
                    tableRows = dt.Rows;

                    dtResult = GetDataFromExcel(dt);
                    dtResult.TableName = row["TABLE_NAME"].ToString();*/
                    //dsExcelFile.Tables.Add(dt);
                }
                return dsCSV;
            }
            catch (Exception ex)
            {
                return null;
                //return ex.ToString();
            }
        }

        /// <summary>
        /// Ham dung de lay ve toan bo thong tin cua file excel
        /// </summary>
        /// <param name="Properties"></param>
        /// <param name="dsCSV"></param>
        /// <returns></returns>
        public DataSet LoadExcel(SetProperties Properties)
        {
            string strreturn = "";
            DataSet dsExcelFile = new DataSet();
            try
            {
                blnSaveFileOnServer = Properties.SaveFileOnServer;
                blnDropExistingTable = Properties.DropExistingTable;
                strCsvDirOnServer = Properties.CsvDirOnServer;
                strTableName = Properties.TableName;
                fi = Properties.FileInformation;

                DataTable dtSheetList = GetSheetList(fi);
                dtSheetList.TableName = "SheetList";
                dsExcelFile.Tables.Add(dtSheetList);

                DataSet dsCSV = null;
                DataSet tmp = null;
                foreach( DataRow row in dtSheetList.Rows )
                {
                    tmp = GetExcelTable(fi, row["TABLE_NAME"].ToString());
                    if (dsCSV != null)
                    {
                        if (TableLib.HasData(tmp))
                            dsCSV.Merge(tmp);
                    }
                    else
                    {
                        dsCSV = tmp;
                    }
                    //DataTable dt = TrimExcelFile(dsCSV.Tables[0]);

                    /*DataColumnCollection tableColumns;
                    DataRowCollection tableRows;

                    tableColumns = dt.Columns;
                    tableRows = dt.Rows;

                    dtResult = GetDataFromExcel(dt);
                    dtResult.TableName = row["TABLE_NAME"].ToString();*/
                    //dsExcelFile.Tables.Add(dt);
                }
                return dsCSV;
            }
            catch (Exception ex)
            {
                return null;
                //return ex.ToString();
            }
        }

        private OleDbConnection ConnectFile(FileInfo filetable)
        {
            try
            {
                string strServerPath = filetable.FullName;
                // string 
                //string connect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strServerPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                string connect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strServerPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
                OleDbConnection objOleDBConn;
                objOleDBConn = new OleDbConnection(connect);
                objOleDBConn.Open();
                //MyMessage.ShowError(filetable.FullName);
                return objOleDBConn;
            }
            catch (Exception ex)
            {
                //MyMessage.ShowError(ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Day la ham DAU TIEN duoc goi de import du lieu tu excel bang cach PHAN TRANG
        /// </summary>
        /// <param name="filetable"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public DataSet GetExcelTable(FileInfo filetable, string SheetName, int startRow, int endRow)
        {
            DataSet ds = new DataSet();
            string str;
            try
            {
                OleDbConnection objOleDBConn = ConnectFile(filetable);
                if (objOleDBConn != null)
                {
                    string sqlSelect;
                    OleDbDataAdapter objOleDBDa;
                    // objOleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sqlSelect = "SELECT IMEI FROM [" + SheetName + "] where ID >= " + startRow + " and ID <= " + endRow;
                    objOleDBDa = new OleDbDataAdapter(sqlSelect, objOleDBConn);
                    objOleDBDa.Fill(ds);
                    //MyMessage.ShowError("Tim duoc so dong la " + ds.Tables[0].Rows.Count);
                    objOleDBConn.Close();
                }
            }
            catch (Exception ex)
            {
                str = ex.Message;
                //MyMessage.ShowError(str);
            }
            return ds;
        }

        /// <summary>
        /// Day la ham DAU TIEN duoc goi de import du lieu tu excel
        /// </summary>
        /// <param name="filetable"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public DataSet GetExcelTable(FileInfo filetable, string SheetName)
        {
            DataSet ds = new DataSet();
            string str;
            try
            {
                OleDbConnection objOleDBConn = ConnectFile(filetable);
                if (objOleDBConn != null)
                {
                    string sqlSelect;
                    OleDbDataAdapter objOleDBDa;
                   // objOleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sqlSelect = "SELECT * FROM [" + SheetName + "]";
                    objOleDBDa = new OleDbDataAdapter(sqlSelect, objOleDBConn);
                    objOleDBDa.Fill(ds);

                    if (TableLib.HasData(ds))
                    {
                        DataSet ds2 = new DataSet();
                        String filter = "";

                        int index = 0;
                        foreach(DataColumn c in ds.Tables[0].Columns)
                        {
                            if(index == (ds.Tables[0].Columns.Count - 1))
                            {
                                if (c.DataType == Type.GetType("System.String"))
                                    filter += "[" + c.ColumnName + "]" + " <> ''";
                                else
                                    filter += "[" + c.ColumnName + "]" + " > 0";
                            }
                            else
                            {
                                if (c.DataType == Type.GetType("System.String"))
                                    filter += "[" + c.ColumnName + "]" + " <> '' or ";
                                else
                                    filter += "[" + c.ColumnName + "]" + " > 0 or ";
                            }
                            index++;
                        }
                        
                        ds2.Tables.Add(TableLib.CreateTable(ds.Tables[0], filter));
                        return ds2;
                    }

                    //MyMessage.ShowError("Tim duoc so dong la " + ds.Tables[0].Rows.Count);
                    objOleDBConn.Close();
                }
            }
            catch (Exception ex)
            {
                str = ex.Message;
                //MyMessage.ShowError(str);
            }
            return null;
        }

        public DataTable GetSheetList(FileInfo filetable)
        {
            DataTable dt = new DataTable();
            string str;
            try
            {
                OleDbConnection objOleDBConn = ConnectFile(filetable);
                if (objOleDBConn != null)
                {
                    dt = objOleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    objOleDBConn.Close();
                }
            }
            catch (Exception ex)
            {
                str = ex.Message;
            }
            return dt;
        }

        /// <summary>
        /// Ham dung de xoa cac cot rong trong file excel
        /// </summary>
        /// <param name="dtExcel">Datatable chua file excel</param>
        /// <returns></returns>
        private DataTable TrimExcelFile( DataTable dtExcel)
        {
            //Mang dung de chua nhung cot can phai xoa
            ArrayList delColumns = new ArrayList();

            int i = 0; //Bien dung de dem so thu tu
            DataColumn column = null;
            DataRow row = null;
            IEnumerator ie = dtExcel.Columns.GetEnumerator();
            IEnumerator ieRow = dtExcel.Rows.GetEnumerator(); //Enum dung de chua dong file excel

            bool flag = false;
            while( ie.MoveNext() )
            {
                flag = false;
                column = (DataColumn)ie.Current;
                ieRow.Reset();
                while( ieRow.MoveNext() )
                {
                    row = (DataRow)ieRow.Current;
                    if (row[column.ColumnName] != null && row[column.ColumnName].ToString().Trim() != "")
                    {
                        flag = true;
                        break;
                    }
                }
                if(!flag)
                    delColumns.Add(column.ColumnName);
            }

            if (delColumns != null && delColumns.Count > 0)
            {
                IEnumerator ie2 = delColumns.GetEnumerator();
                while (ie2.MoveNext())
                {
                    dtExcel.Columns.Remove(ie2.Current.ToString());
                }
            }

            return dtExcel;
        }

        private DataTable GetDataFromExcel(DataTable dtExcel)
        {
            try
            {
                int rowStart = 0;
                int coloumnStart = 0;
                //Lay ve vi tri bat dau cua du lieu trong file excel
                GetStartData(dtExcel.Rows, ref rowStart, ref coloumnStart);

                //Tao table de chua thong tin cua file excel
                DataTable dt = new DataTable();

                //Tao cac cot cho table ( la dong header cua file excel ) 
                for ( int i = coloumnStart; i < dtExcel.Columns.Count; i++ )
                {
                    dt.Columns.Add( new DataColumn(dtExcel.Rows[rowStart][dtExcel.Columns[i].ColumnName].ToString()));
                }


                //Row dung de chua thong tin cua file excel
                DataRow dtRow = null;
                for (int i = rowStart + 1; i < dtExcel.Rows.Count; i++)
                {
                    int count = 0; //So thu tu cua cot cua row chua thong tin file excel
                    dtRow = dt.NewRow();

                    //Kiem tra dong excel nay co du lieu ko ( khong co du lieu la tat ca cac cell deu rong)
                    bool isNull = true;
                    for (int j = coloumnStart; j < dtExcel.Columns.Count; j++)
                    {
                        if (dtExcel.Rows[i][j].ToString() != "")
                        {
                            isNull = false;
                            break;
                        }
                    }

                    if (!isNull)
                    {
                        for (int j = coloumnStart; j < dtExcel.Columns.Count; j++)
                        {
                            dtRow[count] = dtExcel.Rows[i][j].ToString();
                            count++;
                        }
                        dt.Rows.Add(dtRow);
                    }
                }

                return dt;
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
        }

        private string BuildStringParameter(string param)
        {
            return "N'" + param + "', ";
        }

        private string BuildNumberParameter(string param)
        {
            return param + ", ";
        }


        private string InsertRecordsOfImage(DataColumnCollection tableColumns, DataRowCollection tableRows)
        {
            string strreturn = "";
            int rowscreated = 0;
            string slq = "";

            try
            {
                foreach (DataRow row in tableRows)
                {
                    //Build Insert Query string to insert records
                    string sqlinsert = "INSERT INTO " + strTableName + "(";
                    string sqlvalues = "VALUES (";

                    object[] rowItems = row.ItemArray;
                    // loop through each column in a specific DataTable.
                    int ctrColumn = 0;
                    foreach (DataColumn dc in tableColumns)
                    {
                        #region Build cac cot can insert
                        if (ctrColumn < tableColumns.Count - 1)
                        {
                            switch (ctrColumn)
                            {
                                case 0:
                                    sqlinsert += "[WapCode]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 1:
                                    sqlinsert += "[ImageName]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 2:
                                    sqlinsert += "[ImagePath]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 3:
                                    sqlinsert += "[ImagePathOrg_0]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 4:
                                    sqlinsert += "[ImagePathOrg_1]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 5:
                                    sqlinsert += "[ImagePathOrg_2]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 6:
                                    sqlinsert += "[ImagePathOrg_3]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 7:
                                    sqlinsert += "[Width]" + ",";
                                    sqlvalues += BuildNumberParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 8:
                                    sqlinsert += "[Height]" + ",";
                                    sqlvalues += BuildNumberParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 9:
                                    sqlinsert += "[CateID]" + ",";
                                    sqlvalues += BuildNumberParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 10:
                                    sqlinsert += "[CateName]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                                case 11:
                                    sqlinsert += "[ImageType]" + ",";
                                    sqlvalues += BuildStringParameter(rowItems[ctrColumn].ToString());
                                    break;
                            }
                        }
                        #endregion
                        ctrColumn++;
                    }

                    sqlinsert += " CreateDate, CreateBy, CreateByName, ActiveDate, ActiveBy, UpdateDate, UpdateBy, Priority, TotalView, Rating, Price, SendPrice, IsActive, TotalDownload";
                    sqlvalues += "@CreateDate, 2,        'trung',      @ActiveDate, 2,       @UpdateDate, 2, 0, 1, 1, 0,0, 1, 1 ";
                    slq = sqlvalues;
                    sqlinsert = sqlinsert + sqlvalues;

                    slq = sqlinsert;
                    //Execute the command

                    rowscreated += SqlHelper.ExecuteNonQuery(Common.Conn, CommandType.Text, slq, new SqlParameter("@CreateDate", DateTime.Now), new SqlParameter("@ActiveDate", DateTime.Now), new SqlParameter("@UpdateDate", DateTime.Now));
                }
                // strreturn = slq;

                strreturn = "Bản ghi được nhập thành công!<br>";
                strreturn += rowscreated.ToString();
                strreturn += " bản ghi là được nhập vào bảng: " + strTableName;
            }
            catch (Exception ae)
            {
              //  strreturn = "242424"; //"Error at Record Number: ";

                //strreturn += rowscreated.ToString(); 
                //strreturn += "<br>Message: " + ae.Message.ToString();
                //strreturn += "<br>" + "Error importing. Please try again";
            }

            return strreturn;
        }

        #region phien ban cu
        /*private string InsertRecordsThueThuNhap(DataColumnCollection tableColumns, DataRowCollection tableRows)
        {
            string strreturn = "";
            int rowscreated = 0;

            OpenConnection();
            string slq = "";

            try
            {

                int rowStart = 0;
                int coloumnStart = 0;
                GetStartData(tableRows, ref rowStart, ref coloumnStart);

                //Tao Cau Lenh INSERT SQL

                //Tao Phan Dau Cua Cau Lenh ( Gom Cac Cot Can Insert )

                string sqlinsert = "INSERT INTO " + strTableName + " (";

                DataColumn coloumn = null;
                // coloumnStart + 1 tuc la bo qua cot stt

                for (int i = coloumnStart + 1; i < tableColumns.Count; i++)
                {
                    switch (i)
                    {
                        case 3:
                            sqlinsert += "[MaSoThue]";
                            break;
                        case 1:
                            sqlinsert += "[HoTen],";
                            break;
                    }
                }

                sqlinsert += ")";


                //Tao phan sau cua cau lenh ( phan values )
                //Va Chay Cau Lenh

                //rowStart + 2 la bo qua dong tieu de va danh so thu tu cua cot
                for (int rIndex = rowStart + 4; rIndex < tableRows.Count; rIndex++)
                {
                    DataRow row = tableRows[rIndex];
                    bool flag = true;
                    try
                    {
                        //Build Insert Query string to insert records

                        string sqlvalues = " VALUES (";

                        object[] rowItems = row.ItemArray;
                        // loop through each column in a specific DataTable.

                        for (int i = coloumnStart + 1; i < tableColumns.Count; i++)
                        {
                            switch (i)
                            {
                                case 3:
                                case 1:
                                    if (rowItems[i] != null && rowItems[i].ToString().Trim() != "")
                                    {
                                        sqlvalues += "N'" + rowItems[i].ToString() + "',";
                                    }
                                    else
                                    {
                                        flag = false;
                                    }
                                    break;
                            }
                        }

                        if (flag)
                        {
                            sqlvalues = sqlvalues.Substring(0, sqlvalues.Length - 1);
                            sqlvalues += ")";

                            slq = sqlinsert + sqlvalues;

                            //Execute the command
                            cmd = new SqlCommand(slq, conn);
                            rowscreated += cmd.ExecuteNonQuery();

                            strreturn = "Bản ghi nhân viên " + rowItems[0] + " được nhập thành công!\n";
                        }
                    }
                    catch (SqlException se)
                    {
                        strreturn = "Xảy ra lỗi khi cập nhật bản ghi nhân viên dòng thứ " + (rIndex + 1) + " !\n";
                    }
                }
                // strreturn = slq;

            }
            catch (Exception ae)
            {
                //  strreturn = "242424"; //"Error at Record Number: ";
                //strreturn += rowscreated.ToString();
                strreturn += "<br>Message: " + ae.Message.ToString();
                strreturn += "<br>" + "Error importing. Please try again";
            }
            finally
            {
                CloseConnection();
            }

            return strreturn;
        }*/
        #endregion

        #endregion

        #region Export Functionality
        #endregion
    }
}
